	package fi.iki.joker.mobilogger.db;

import java.io.File;
import java.io.FileNotFoundException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;
import fi.iki.joker.mobilogger.editor.SpannableBufferHelper;

/**
 * DBUtil contains the utility methods to manipulate SQLLite3 db of
 * Mobilogger application.
 * TODO: The insert and update methods are not safe (to SQL injection etc)
 * @author juha
 *
 */

public class DBUtil {
	
	private SQLiteDatabase db = null;
	private static final String TAG = "DBUtil";
	private static final String CREATE_MEMO_TABLE = 
				"CREATE TABLE IF NOT EXISTS MEMO ("+
				"id INTEGER PRIMARY KEY AUTOINCREMENT,"+
				"title TEXT,"+
				"contents TEXT,"+
				"created TEXT"+
				");";
	private static final String CREATE_ENTRY_TABLE = 
				"CREATE TABLE IF NOT EXISTS ENTRY ("+
				"id INTEGER PRIMARY KEY AUTOINCREMENT,"+
				"published_in INTEGER," +
				"title TEXT,"+
				"blogentry TEXT,"+
				"created TEXT"+
				");";
	private static final String CREATE_BLOGCONFIG_TABLE =
				"CREATE TABLE IF NOT EXISTS BLOGCONFIG ("+
				"id INTEGER PRIMARY KEY AUTOINCREMENT,"+
				"blogname TEXT,"+
				"rssfeedurl TEXT,"+
				"lastwritten TEXT,"+
				"lastentry INT," +
				"postconfig TEXT," +
				"username TEXT," +
				"password TEXT," +
				"postmethod INT"+
				");";	
	private static final String CREATE_SETTINGS_TABLE = 
				"CREATE TABLE IF NOT EXISTS SETTINGS ("+
				"id INTEGER PRIMARY KEY AUTOINCREMENT,"+
				"settingname TEXT,"+
				"settingvalue TEXT"+
				");";
	private static final String CREATE_IMAGEREPO_TABLE =
				"CREATE TABLE IF NOT EXISTS IMAGEREPO ("+
				"id INTEGER PRIMARY KEY AUTOINCREMENT, "+
				"title TEXT, " +
				"username TEXT, " +
				"password TEXT, " +
				"interfacetype INT," +
				"postconfig INT"+
				");";
	private static final String MEMO_TABLE_INITIAL = "INSERT INTO MEMO (title,contents,created) "+
													"VALUES ('demomemo','Remember to take a picture of the site visited at 12th!','01-12-2007');";
	
	/**
	 * This public method can be used to test the readiness of the db, while closing the db handle and executing no queries.
	 * @param app
	 * @return
	 */
	
	public boolean testDatabaseReady(Context app) {
		MobiLoggerDBHelper helper = new MobiLoggerDBHelper(app,DBConstants.DB_NAME,null, DBConstants.DB_VER);
		Log.d(TAG,"testDataBaseReady Called... helper is "+helper);
		db = helper.getWritableDatabase();
		Log.d(TAG,"getWritableDatabase called, db is "+db);
		if(db == null) {
			return false;
		} else {
			db.close();
			return true;
		}
	}
	
	private boolean isDatabaseReady(Context app) {
		MobiLoggerDBHelper helper = new MobiLoggerDBHelper(app,DBConstants.DB_NAME,null, DBConstants.DB_VER);
		Log.d(TAG,"isDataBaseReady Called... helper is "+helper);
		db = helper.getWritableDatabase();
		Log.d(TAG,"getWritableDatabase called, db is "+db);
		if(db == null) {
			return false;
		} else {
			return true;
		}
	}
			
	public boolean insert(Context app, MemoBean m) {
		String sql = "INSERT INTO MEMO (title, contents, created) VALUES "+
		"( ?, ?, ?)";
		if(isDatabaseReady(app)) {
			try {
				SQLiteStatement insertStmt = db.compileStatement(sql);
				insertStmt.bindString(1, m.getTitle());
				insertStmt.bindString(2, m.getContents());
				insertStmt.bindString(3, dateToString(m.getCreated()));
				insertStmt.execute();
			} catch (SQLException e) {
				Log.e(TAG, "SQLException while executing: "+sql+"\n");
				return false;
			}
			db.close();
			return true;
		} else {
			Log.d(TAG,"DB was not open while inserting MemoBean!");
			return false;
		}
	}
	
	public boolean insert(Context app,BlogConfigBean b) {
		String sql = "INSERT INTO BLOGCONFIG (blogname, rssfeedurl, "+
		"lastwritten, lastentry, postconfig, postmethod, username, password) VALUES "+
		"(?,?,?,?,?,?,?,?)";
		
		if(isDatabaseReady(app)) {
			try {
				SQLiteStatement insertStmt = db.compileStatement(sql);
				insertStmt.bindString(1,b.getBlogname());
				//seems that binding null crashes the VM on the emulator m5-rc15_linux-x86
				insertStmt.bindString(2,(b.getRssfeedurl() == null)? "" : b.getRssfeedurl());
				insertStmt.bindString(3,(b.getLastwritten() == null)? "" : b.getLastwritten());
				insertStmt.bindLong(4,b.getLastentry());
				insertStmt.bindString(5,b.getPostConfig().toString());
				insertStmt.bindLong(6,b.getPostmethod());
				insertStmt.bindString(7,b.getUsername());
				insertStmt.bindString(8,b.getPassword());
				insertStmt.execute();
			} catch (SQLException e) {
				Log.e(TAG, "SQLException while executing: "+sql+"\n");
				return false;
			}
			db.close();
			return true;
		} else {
			Log.d(TAG,"DB was not open while inserting BlogConfigBean!");
			return false;
		}
	}
	
	public boolean insert(Context app,BlogEntryBean b) {
		SpannableBufferHelper helper = new SpannableBufferHelper();
		String sql = "INSERT INTO ENTRY (published_in, title, blogentry, created) VALUES "+
		"(?,?,?,?)";
		if(isDatabaseReady(app)) {
			try {
				SQLiteStatement insertStmt = db.compileStatement(sql);
				insertStmt.bindLong(1,b.getPublishedIn());
				insertStmt.bindString(2, b.getTitle());
				insertStmt.bindString(3,helper.getSerializedSequence(b.getBlogEntry()));
				insertStmt.bindString(4,dateToString(b.getCreated()));
				insertStmt.execute();
			} catch (SQLException e) {
				Log.e(TAG, "SQLException while executing: "+sql+"\n");
				return false;
			}
			db.close();
			return true;
		} else {
			Log.d(TAG,"DB was not open while inserting BlogEntryBean!");
			return false;
		}
	}
	
	public boolean insert(Context app,ImageRepoBean b) {
		String sql = "INSERT INTO IMAGEREPO (title, username, password, interfacetype, postconfig) VALUES "+
		"(?,?,?,?,?)";
		if(isDatabaseReady(app)) {
			try {
				SQLiteStatement insertStmt = db.compileStatement(sql);
				insertStmt.bindString(1,b.getTitle());
				insertStmt.bindString(2,b.getUsername());
				insertStmt.bindString(3, b.getPassword());
				insertStmt.bindLong(4,b.getInterfaceType());
				insertStmt.bindString(5,b.getPostconfig());
				insertStmt.execute();
			} catch (SQLException e) {
				Log.e(TAG, "SQLException while executing: "+sql+"\n");
				return false;
			}
			db.close();
			return true;
		} else {
			Log.d(TAG,"DB was not open while inserting BlogEntryBean!");
			return false;
		}
	}
	
	public long insertAndReturnRow(Context app,BlogEntryBean b) {
		SpannableBufferHelper helper = new SpannableBufferHelper();
		ContentValues newRow = new ContentValues();
		newRow.put("published_in", b.getPublishedIn());
		newRow.put("title", b.getTitle());
		newRow.put("blogentry", helper.getSerializedSequence(b.getBlogEntry()));
		newRow.put("created", dateToString(b.getCreated()));
		if(isDatabaseReady(app)) {
			long res = -1;
			try {
				res = this.db.insert("ENTRY", "NULL", newRow);
			} catch (SQLException e) {
				Log.e(TAG, "SQLException while executing insert query: "+e.getMessage()+"\n");
				return -1;
			}
			db.close();
			return res;
		} else {
			Log.d(TAG,"DB was not open while inserting SettingsBean!");
			return -1;
		}
	}
	
	public boolean insert(Context app,SettingBean s) {
		String sql = "INSERT INTO SETTINGS (settingname, settingvalue) VALUES "+
		"(?,?)";
		if(isDatabaseReady(app)) {
			try {
				SQLiteStatement insertStmt = db.compileStatement(sql);
				insertStmt.bindString(1,s.getSettingkey());
				insertStmt.bindString(2,s.getSettingvalue());
				insertStmt.execute();
			} catch (SQLException e) {
				Log.e(TAG, "SQLException while executing: "+sql+"\n");
				return false;
			}
			return true;
		} else {
			Log.d(TAG,"DB was not open while inserting SettingsBean!");
			return false;
		}	
	}
	
	public MemoBean[] getMemos(Context app) {
		final String SQL = "SELECT id, title, contents, created from MEMO";
		Cursor cur = null;
		MemoBean[] mbs = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL, null);
		} else {
			Log.e(TAG,"Database is not open when getting memos and IDs!");
			return null;
		}
		
		if((cur != null) && (cur.getCount() > 0)) {
			mbs = new MemoBean[cur.getCount()];
			cur.moveToFirst();
			for(int i = 0; i < cur.getCount(); i++) {
				MemoBean b = new MemoBean();
				b.setId(cur.getInt(0));
				b.setTitle(cur.getString(1));
				b.setContents(cur.getString(2));
				b.setCreated(stringToDate(cur.getString(3)));
				mbs[i] = b;
				cur.moveToNext();
			}
			cur.close();
		}
		db.close();
		return mbs;
	}
	
	/**
	 * Gets the IDs and blog names from the DB, does not populate other fields of 
	 * the bean 
	 * @return Array of BlogConfigBeans
	 */
	
	public BlogConfigBean[] getConfiguredBlogs(Context app) {
		final String SQL = "SELECT id, blogname from BLOGCONFIG";
		Cursor cur = null;
		BlogConfigBean[] bcbs = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL, null);
		} else {
			Log.e(TAG,"Database is not open when getting blog names and IDs!");
			return null;
		}
		if((cur != null) && (cur.getCount() > 0)) {
			bcbs = new BlogConfigBean[cur.getCount()];
			cur.moveToFirst();
			for(int i = 0; i < cur.getCount(); i++) {
				BlogConfigBean b = new BlogConfigBean();
				b.setId(cur.getInt(0));
				b.setBlogname(cur.getString(1));
				bcbs[i] = b;
				cur.moveToNext();
			}
			cur.close();
		}
		db.close();
		return bcbs;
	}
	
	public LinkedList<BlogConfigBean> getBlogNames(Context ctx) {
		LinkedList<BlogConfigBean> res = null;
		BlogConfigBean [] bcbs = null;
		bcbs = getConfiguredBlogs(ctx);
		if(bcbs != null) {
			res = new LinkedList<BlogConfigBean>();
			for(int i = 0; i < bcbs.length; i++) {
				res.add(bcbs[i]);
			}
			return res;
		} else {
			Log.e(TAG,"Failed to get the blog configs from DB!");
			return null;
		}
	}
	
	public LinkedList<BlogEntryBean> getBlogEntriesByConfig(Context ctx, int id) {
		LinkedList<BlogEntryBean> res = null;
		BlogEntryBean [] bes = null;
		bes = getEntriesByBlogId(""+id,ctx);
		if(bes != null) {
			res = new LinkedList<BlogEntryBean>();
			for(int i = 0; i < bes.length; i++) {
				res.add(bes[i]);
			}
			return res;
		} else {
			Log.e(TAG,"Failed to get the blog entries from DB!");
			return null;
		}
	}

	
	public BlogEntryBean[] getEntriesByBlogId(String blogid,Context c) {
		final String SQL = "SELECT id, title, blogentry, created from ENTRY where published_in = ?"; 
		Cursor cur = null;
		BlogEntryBean[] bcbs = null;
		SpannableBufferHelper helper = new SpannableBufferHelper();
		if(isDatabaseReady(c)) {
			cur = db.rawQuery(SQL, new String[]{blogid});
		} else {
			Log.e(TAG,"Database is not open when getting blog entries!");
			return null;
		}
		if((cur != null) && (cur.getCount() > 0)) {
			bcbs = new BlogEntryBean[cur.getCount()];
			cur.moveToFirst();
			for(int i = 0; i < cur.getCount(); i++) {
				BlogEntryBean b = new BlogEntryBean();
				b.setId(cur.getInt(0));
				b.setTitle(cur.getString(1));
				b.setBlogEntry(helper.deSerializeStringToSequence(cur.getString(2),c));
				b.setCreated(stringToDate(cur.getString(4)));
				bcbs[i] = b;
				cur.moveToNext();
			}
			cur.close();
		}
		db.close();
		return bcbs;	
	}
	
	public BlogEntryBean[] getBlogEntries(Context c) {
		final String SQL = "SELECT id, title, blogentry, created from ENTRY"; 
		SpannableBufferHelper helper = new SpannableBufferHelper();
		Cursor cur = null;
		BlogEntryBean[] bcbs = null;
		if(isDatabaseReady(c)) {
			cur = db.rawQuery(SQL,null);
		} else {
			Log.e(TAG,"Database is not open when getting blog entries!");
			return null;
		}
		if((cur != null) && (cur.getCount() > 0)) {
			bcbs = new BlogEntryBean[cur.getCount()];
			cur.moveToFirst();
			for(int i = 0; i < cur.getCount(); i++) {
				BlogEntryBean b = new BlogEntryBean();
				b.setId(cur.getInt(0));
				b.setTitle(cur.getString(1));
				b.setBlogEntry(helper.deSerializeStringToSequence(cur.getString(2),c));
				b.setCreated(stringToDate(cur.getString(3)));
				bcbs[i] = b;
				cur.moveToNext();
			}
			cur.close();
		}
		db.close();
		return bcbs;	
	}
	
	public ImageRepoBean[] getImageRepositories(Context c) {
		final String SQL = "SELECT id, title, username, password, interfacetype, postconfig from IMAGEREPO"; 
		Cursor cur = null;
		ImageRepoBean[] irbs = null;
		if(isDatabaseReady(c)) {
			cur = db.rawQuery(SQL,null);
		} else {
			Log.e(TAG,"Database is not open when getting image repositories!");
			return null;
		}
		if((cur != null) && (cur.getCount() > 0)) {
			irbs = new ImageRepoBean[cur.getCount()];
			cur.moveToFirst();
			for(int i = 0; i < cur.getCount(); i++) {
				ImageRepoBean b = new ImageRepoBean();
				b.setId(cur.getInt(0));
				b.setTitle(cur.getString(1));
				b.setUsername(cur.getString(2));
				b.setPassword(cur.getString(3));
				b.setInterfaceType(cur.getInt(4));
				b.setPostconfig(cur.getString(5));
				irbs[i] = b;
				cur.moveToNext();
			}
			cur.close();
		}
		db.close();
		return irbs;	
	}
	
	public BlogEntryBean getBlogEntryById(Context c, int id) {
		final String SQL = "SELECT id, published_in, title, blogentry, created from ENTRY where id = ?"; 
		Cursor cur = null;
		SpannableBufferHelper helper = new SpannableBufferHelper();
		BlogEntryBean eb = null;
		if(isDatabaseReady(c)) {
			cur = db.rawQuery(SQL, new String[]{""+id});
		} else {
			Log.e(TAG,"Database is not open when getting blog entries!");
			return null;
		}
		if((cur != null) && (cur.getCount() == 1)) {
			eb = new BlogEntryBean();
			cur.moveToFirst();
			eb.setId(cur.getInt(0));
			eb.setPublishedIn(cur.getInt(1));
			eb.setTitle(cur.getString(2));
			eb.setBlogEntry(helper.deSerializeStringToSequence(cur.getString(3),c));
			eb.setCreated(stringToDate(cur.getString(4)));
			cur.close();
		}
		db.close();
		return eb;
	}
	
	public int getBlogIdByTitleAndType(Context app,String title, int type) {
		final String SQL = "SELECT id from ENTRY where title = ? and published_in = ?";
		Cursor cur = null;
		int res = -1;
		if(isDatabaseReady(app)) {
			Log.d(TAG,"Querying: "+SQL+",title="+title+",type="+type);
			cur = db.rawQuery(SQL, new String[]{""+title,""+type});
		} else {
			Log.e(TAG,"Database is not open when getting blog id (by title and type)!");
			return -1;
		}
		if((cur != null) && (cur.getCount() == 1)) {
			cur.moveToFirst();
			res = cur.getInt(0);
			Log.d(TAG,"Got result: "+res);
			cur.close();
		}
		db.close();
		return res;
	}
	
	public BlogConfigBean getBlogConfigById(Context app,int id) {
		final String SQL = 
			"SELECT blogname, rssfeedurl, lastwritten, lastentry, postconfig, username, password, postmethod FROM BLOGCONFIG where id = ?"; 
		Cursor cur = null;
		BlogConfigBean cb = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL, new String[]{""+id});
		} else {
			Log.e(TAG,"Database is not open when getting blog entries!");
			return null;
		}
		if((cur != null) && (cur.getCount() == 1)) {
			cb = new BlogConfigBean();
			cur.moveToFirst();
			cb.setId(id);
			cb.setBlogname(cur.getString(0));
			cb.setRssfeedurl(cur.getString(1));
			cb.setLastwritten(cur.getString(2));
			cb.setLastentry(cur.getInt(3));
			cb.setPostConfig(cur.getString(4));
			cb.setUsername(cur.getString(5));
			cb.setPassword(cur.getString(6));
			cb.setPostmethod(cur.getInt(7));
			cur.close();
		}
		db.close();
		return cb;
	}
	
	public MemoBean getMemo(Context app,int id) {
		final String SQL = 
			"SELECT id, title, contents, created FROM MEMO where id = ?"; 
		Cursor cur = null;
		MemoBean mb = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL, new String[]{""+id});
		} else {
			Log.e(TAG,"Database is not open when getting blog entry with id "+id+"!");
			return null;
		}
		if((cur != null) && (cur.getCount() == 1)) {
			mb = new MemoBean();
			cur.moveToFirst();
			mb.setId(cur.getInt(0));
			mb.setTitle(cur.getString(1));
			mb.setContents(cur.getString(2));
			mb.setCreated(stringToDate(cur.getString(3)));
			cur.close();
		}
		db.close();
		return mb;
	}
	
	public ImageRepoBean getImageRepository(Context app,int id) {
		final String SQL = 
			"SELECT id, title, username, password, interfacetype, postconfig FROM IMAGEREPO where id = ?"; 
		Cursor cur = null;
		ImageRepoBean ib = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL, new String[]{""+id});
		} else {
			Log.e(TAG,"Database is not open when getting image repository with id "+id+"!");
			return null;
		}
		if((cur != null) && (cur.getCount() == 1)) {
			ib = new ImageRepoBean();
			cur.moveToFirst();
			ib.setId(cur.getInt(0));
			ib.setTitle(cur.getString(1));
			ib.setUsername(cur.getString(2));
			ib.setPassword(cur.getString(3));
			ib.setInterfaceType(cur.getInt(4));
			ib.setPostconfig(cur.getString(5));
			cur.close();
		}
		db.close();
		return ib;
	}
	
	/**
	 * Update Memobean in the DB, have ID there in the bean, otherwise it won't
	 * work!
	 * @param mb
	 */
	
	public void update(Context app,final MemoBean mb) {
		int id = -1;
		String SQL = null;
		if(mb != null) {
			id = mb.getId();
		}
		if(id != -1) {
			SQL = "UPDATE MEMO SET "+
			"title = ?"+
			", contents = ?"+
			", created = ?"+
			" WHERE id = ?";
		} else {
			Log.e(TAG,"Failed to update MEMO since the id is not good!");
		}
		if(isDatabaseReady(app)) {
			SQLiteStatement updateStmt = db.compileStatement(SQL);
			updateStmt.bindString(1, mb.getTitle());
			updateStmt.bindString(2, mb.getContents());
			updateStmt.bindString(3, dateToString(mb.getCreated()));
			updateStmt.bindLong(4, mb.getId());
			Log.d(TAG,"Executing: "+SQL);
			updateStmt.execute();
			db.close();
		} else {
			Log.e(TAG,"Database is not open when updating memo entry!");
		}
		Log.d(TAG,"Memo updated with, id = "+id);
	}
	
	/**
	 * Update BlogConfigBean in the DB, have ID there in the bean, otherwise it won't
	 * work!
	 * @param mb
	 */
	
	public void update(Context app,final BlogConfigBean mb) {
		int id = -1;
		String SQL = null;
		if(mb != null) {
			id = mb.getId();
		}
		if(id != -1) {
			SQL = "UPDATE BLOGCONFIG SET "+
			"blogname = ?,"+
			"rssfeedurl = ?,"+
			"lastwritten = ?,"+
			"lastentry = ?,"+
			"postconfig = ?,"+
			"username = ?,"+
			"password = ?,"+
			"postmethod = ? "+
			"WHERE id = ?";
		} else {
			Log.e(TAG,"Failed to update BLOGCONFIG since the id is not good!");
		}
		if(isDatabaseReady(app)) {
			SQLiteStatement updateStmt = db.compileStatement(SQL);
			updateStmt.bindString(1,mb.getBlogname());
			updateStmt.bindString(2,mb.getRssfeedurl());
			updateStmt.bindString(3,mb.getLastwritten());
			updateStmt.bindLong(4,mb.getLastentry());
			updateStmt.bindString(5,mb.getPostConfig().toString());
			updateStmt.bindString(6,mb.getUsername());
			updateStmt.bindString(7,mb.getPassword());
			updateStmt.bindLong(8,mb.getPostmethod());
			updateStmt.execute();
			db.close();
			Log.d(TAG,"Executing: "+SQL);
		} else {
			Log.e(TAG,"Database is not open updating blog config entries!");
		}
		Log.d(TAG,"Blog config updated with, id = "+id);
	}
	/**
	 * Update SettingBean in the DB, have ID there in the bean, otherwise it won't
	 * work!
	 * @param mb
	 */
	
	public void update(Context app,final SettingBean mb) {
		String SQL = null;
		if(mb != null) {
			SQL = "UPDATE SETTINGS SET settingvalue = ? "+
			"WHERE settingname = ?";
		} else {
			Log.e(TAG,"Failed to update SETTINGS since the input bean is null!");
		}
		if(isDatabaseReady(app)) {
			Log.d(TAG,"Executing: "+SQL);
			SQLiteStatement updateStmt = db.compileStatement(SQL);
			updateStmt.bindString(1,mb.getSettingkey());
			updateStmt.bindString(2,mb.getSettingvalue());
			updateStmt.execute();
			db.close();
		} else {
			Log.e(TAG,"Database is not open when updating settings!");
		}
		Log.d(TAG,"Settings updated with,  key: "+mb.getSettingkey()+" Value:"+mb.getSettingvalue());
	}
	/**
	 * Update BlogEntryBean in the DB, have ID there in the bean, otherwise it won't
	 * work!
	 * @param mb
	 */
	
	public void update(Context app,final BlogEntryBean mb) {
		SpannableBufferHelper helper = new SpannableBufferHelper();
		int id = -1;
		String SQL = null;
		if(mb != null) {
			id = mb.getId();
		}
		if(id != -1) {
			SQL = "UPDATE ENTRY SET "+
			"published_in = ?,"+
			"title = ?,"+
			"blogentry = ?,"+
			"created = ? "+
			"WHERE id = ?";
		} else {
			Log.e(TAG,"Failed to update BLOGENTRY since the id is not good!");
		}
		if(isDatabaseReady(app)) {
			SQLiteStatement updateStmt = db.compileStatement(SQL);
			updateStmt.bindLong(1,mb.getPublishedIn());
			updateStmt.bindString(2, mb.getTitle());
			updateStmt.bindString(3,helper.getSerializedSequence(mb.getBlogEntry()));
			updateStmt.bindString(4,dateToString(mb.getCreated()));
			updateStmt.bindLong(5, mb.getId());
			updateStmt.execute();
			db.close();
			Log.d(TAG,"Executing: "+SQL);
		} else {
			Log.e(TAG,"Database is not open when updating blog entry!");
		}
		Log.d(TAG,"Blog entry updated with, id = "+id);
	}
	
	/**
	 * Update ImageRepoBean in the DB, have ID there in the bean, otherwise it won't
	 * work!
	 * @param ib
	 */
	
	public void update(Context app,final ImageRepoBean ib) {
		int id = -1;
		String SQL = null;
		if(ib != null) {
			id = ib.getId();
		}
		if(id != -1) {
			SQL = "UPDATE IMAGEREPO SET "+
			"title = ?,"+
			"username = ?,"+
			"password = ?,"+
			"interfacetype = ?, "+
			"postconfig = ? "+
			"WHERE id = ?";
		} else {
			Log.e(TAG,"Failed to update IMAGEREPO since the id is not good!");
		}
		if(isDatabaseReady(app)) {
			Log.d(TAG,"Executing: "+SQL);
			SQLiteStatement updateStmt = db.compileStatement(SQL);
			updateStmt.bindString(1, ib.getTitle());
			updateStmt.bindString(2,ib.getUsername());
			updateStmt.bindString(3,ib.getPassword());
			updateStmt.bindLong(4,ib.getInterfaceType());
			updateStmt.bindString(5,ib.getPostconfig());
			updateStmt.bindLong(6, ib.getId());
			updateStmt.execute();
			db.close();
		} else {
			Log.e(TAG,"Database is not open when updating imagerepository!");
		}
		Log.d(TAG,"Repository updated with, id = "+id);
	}
	
	
	public BlogConfigBean getBlogConfigByUserPassAndType(Context app,int postmethod, String username, String password) {
		final String SQL = 
			"SELECT blogname, rssfeedurl, lastwritten, lastentry, postconfig, "+
					"username, password, postmethod FROM BLOGCONFIG where postmethod = ? and "+
					"username = ? and password = ?";
		Cursor cur = null;
		BlogConfigBean cb = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL, new String[]{""+postmethod,username,password});
		} else {
			Log.e(TAG,"Database is not open when getting blog entries!");
			return null;
		}
		if((cur != null) && (cur.getCount() == 1)) {
			cb = new BlogConfigBean();
			cur.moveToFirst();
			cb.setBlogname(cur.getString(0));
			cb.setRssfeedurl(cur.getString(1));
			cb.setLastwritten(cur.getString(2));
			cb.setLastentry(cur.getInt(3));
			cb.setPostConfig(cur.getString(4));
			cb.setUsername(cur.getString(5));
			cb.setPassword(cur.getString(6));
			cb.setPostmethod(cur.getInt(7));
			cur.close();
		}
		db.close();
		return cb;
	}
	
	public void deleteBlogConfigItem(Context app,int id) {
		//final String SQL = "DELETE from BLOGCONFIG WHERE id = ?";
		if(isDatabaseReady(app)) {
			db.delete("BLOGCONFIG","id=?",new String[]{""+id});
			db.close();
		}
	}
	public void deleteBlogEntryItem(Context app,int id) {
		//final String SQL = "DELETE from ENTRY WHERE id = ?";
		if(isDatabaseReady(app)) {
			db.delete("ENTRY", "id=?", new String[]{""+id});
			db.close();
		}
	}
	public void deleteMemoItem(Context app,int id) {
		//final String SQL = "DELETE from MEMO WHERE id = ?";
		if(isDatabaseReady(app)) {
			db.delete("MEMO", "id=?", new String[]{""+id});
			db.close();
		}
	}
	public void deleteSettingsItem(Context app,int id) {
		//final String SQL = "DELETE from SETTINGS WHERE id = ?";
		if(isDatabaseReady(app)) {
			db.delete("SETTINGS","id=?", new String[]{""+id});
			db.close();
		}
	}
	
	public void deleteImageRepositoryItem(Context app,int id) {
		//final String SQL = "DELETE from IMAGEREPO WHERE id = ?";
		if(isDatabaseReady(app)) {
			db.delete("IMAGEREPO","id=?", new String[]{""+id});
			db.close();
		}
	}
	
	private Date stringToDate(String dateStr) {
		SimpleDateFormat df = new SimpleDateFormat(DBConstants.DB_DATE_FORMAT);
		Date d = null;
		try {
			d = df.parse(dateStr);
		} catch (ParseException e) {
			Log.e(TAG,"Failed to parse date to SQL date... Revert to now.");
			return new Date(System.currentTimeMillis());
		}
		return d;
	}
	
	private String dateToString(Date date) {
		SimpleDateFormat df = new SimpleDateFormat(DBConstants.DB_DATE_FORMAT); 
		return df.format(date);
	}
	
	/**
	 * Override all config settings to the default values
	 */
	
	private void createDefaultSettings(Context app) {
		if(isDatabaseReady(app)) {
			Log.d(TAG,"Deleting the settings table contents.");
			db.delete("SETTINGS",null,null);
			db.close();
		}
		//In this version, there's three settings,
		// - Language
		// - Default author name
		// - Default image library
		// DBConstants has corresponding key constants and default values.
		SettingBean defaultlanguage = new SettingBean();
		defaultlanguage.setSettingname(DBConstants.SETTING_KEY_LANGUAGE);
		defaultlanguage.setSettingvalue(DBConstants.SETTING_DEFAULT_VALUE_LANGUAGE);
		this.insert(app, defaultlanguage);
		
		SettingBean defaultauthor = new SettingBean();
		defaultauthor.setSettingname(DBConstants.SETTING_KEY_AUTHOR);
		defaultauthor.setSettingvalue(DBConstants.SETTING_DEFAULT_VALUE_AUTHOR);
		this.insert(app,defaultauthor);
		
		SettingBean defaultimlib = new SettingBean();
		defaultimlib.setSettingname(DBConstants.SETTING_KEY_IMAGELIB);
		defaultimlib.setSettingvalue(DBConstants.SETTING_DEFAULT_VALUE_IMAGELIB);
		this.insert(app, defaultimlib);
	}
	
	/**
	 * Fetch all blog settins from the db and return as an array of beans.
	 * 
	 */
	
	public SettingBean[] getSavedSettings(Context app) {
		final String SQL = "SELECT settingname, settingvalue from SETTINGS"; 
		Cursor cur = null;
		SettingBean[] settings = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL,null);
		} else {
			Log.e(TAG,"Database is not open when getting settings!");
			return null;
		}
		if((cur != null) && (cur.getCount() > 0)) {
			settings = new SettingBean[cur.getCount()];
			cur.moveToFirst();
			for(int i = 0; i < cur.getCount(); i++) {
				SettingBean b = new SettingBean();
				b.setSettingname(cur.getString(0));
				b.setSettingvalue(cur.getString(1));
				settings[i] = b;
				cur.moveToNext();
			}	
			cur.close();
		}
		db.close();
		return settings;	
	}
	
	public SettingBean getSettingBeanByKey(Context app, String key) {
		final String SQL = 
			"SELECT settingvalue FROM SETTINGS where settingname = ?";
		Cursor cur = null;
		SettingBean cb = null;
		if(isDatabaseReady(app)) {
			cur = db.rawQuery(SQL, new String[]{""+key});
		} else {
			Log.e(TAG,"Database is not open when getting blog entries!");
			return null;
		}
		if((cur != null) && (cur.getCount() == 1)) {
			cb = new SettingBean();
			cur.moveToFirst();
			cb.setSettingname(key);
			cb.setSettingvalue(cur.getString(0));
			cur.close();
		}
		db.close();
		return cb;
	}
}
	
